﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Weight.Base;
using Weight.Model;
using Dapper;

namespace Weight.Dal
{
    public  class DalEmployee
    {
        /// <summary>
        /// 查询所有的账号
        /// </summary>
        /// <param name="fields"></param>
        /// <returns></returns>
        public IEnumerable<EmployeeEntity> QueryAll(string fields)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                if(string.IsNullOrEmpty(fields))
                {
                    fields = " * ";
                }
                string  sql = " select " + fields + " from  Employee  order by Id desc";

                return conn.Query<EmployeeEntity>(sql);
            }
        }

        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="account">账号</param>
        /// <param name="pwd">密码</param>
        /// <returns></returns>
        public EmployeeEntity Login(string account, string pwd)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = " select * from  Employee where Account=@account and Pwd=@pwd";
                return conn.QueryFirstOrDefault<EmployeeEntity>(sql,new { account,pwd});
            }
        }

        /// <summary>
        /// 根据名称查询所有的账号
        /// </summary>
        /// <param name="fields"></param>
        /// <returns></returns>
        public IEnumerable<EmployeeEntity> QueryByName(string fields,string account)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                if(!string.IsNullOrEmpty(account))
                {
                    account = "%" + account + "%";
                }
                if (string.IsNullOrEmpty(fields))
                {
                    fields = " * ";
                }
                string sql = " select " + fields + " from  Employee where Account like @account order by CreateTime desc";

                return conn.Query<EmployeeEntity>(sql,new {account });
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Add(EmployeeEntity entity)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" insert into Employee(Id,Account,Pwd,Name,Roles,IsAdmin,Shits,IsValid,S1,S2,S3,S4,S5,Simplicity,CreateTime,Remark,IsSyn)values(
                @Id,@Account,@Pwd,@Name,@Roles,@IsAdmin,@Shits,@IsValid,@S1,@S2,@S3,@S4,@S5,@Simplicity,@CreateTime,@Remark,@IsSyn)";
                return conn.Execute(sql, entity) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(EmployeeEntity entity)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = @" update Employee set Account=@Account,Pwd=@Pwd,Name=@Name,Roles=@Roles,IsAdmin=@IsAdmin,Shits=@Shits,IsValid=@IsValid,S1=@S1,S2=@S2,S3=@S3,
            S4=@S4,S5=@S5,Simplicity=@Simplicity,CreateTime=@CreateTime,Remark=@Remark,IsSyn=@IsSyn where Id=@Id";
                return conn.Execute(sql, entity) > 0 ? true : false;
            }
        }



        /// <summary>
        /// 判断登录账号是否已经存在
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public bool AccountIsExist(string account)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from  Employee where Account=@account ";
                return conn.ExecuteScalar<int>(sql, new { account }) > 0 ? true : false;
            }
        }

        /// <summary>
        /// 判断登录账号是否已经存在
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public bool AccountIsExist(string account,string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select count(1) from  Employee where Account=@account and Id<>@id ";
                return conn.ExecuteScalar<int>(sql, new { account,id }) > 0 ? true : false;
            }
        }


        /// <summary>
        /// 根据Id 获取员工信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public EmployeeEntity GetEmpById(string id)
        {
            using(IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "select * from  Employee where Id=@id ";
                return conn.QueryFirstOrDefault<EmployeeEntity>(sql, new { id });
            }
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id">编号</param>
        /// <returns></returns>
        public bool Delete(string id)
        {
            using (IDbConnection conn = SessionFactory.CreateConnection())
            {
                string sql = "delete  from  Employee where Id=@id ";
                return conn.Execute(sql, new { id }) > 0 ? true:false;
            }
        }

    }
}
